	insert into Region ( RegionID, RegionName) 
	values(1, 'Scotland');
	insert into Region ( RegionID, RegionName) 
	values(2, 'Wales');
	insert into Region ( RegionID, RegionName) 
	values(3, 'South West');
	insert into Region ( RegionID, RegionName) 
	values(4, 'South East');

	insert into City ( cityId ,cityName ,regionID) 
	values(1, 'London',4);
	insert into City ( cityId ,cityName ,regionID) 
	values(2, 'Glasgow',1);
	insert into City ( cityId ,cityName ,regionID) 
	values(3, 'Cardif',2);
	insert into City ( cityId ,cityName ,regionID) 
	values(4, 'Bristol',3);

	insert into Branch ( branchNo, branchName ,branchAddress ,cityID , postcode ,telNo) 
	values(1, 'To-Rent London', '14 Grove Street', 1, 'SW1XSDJ', 07820305986);
	insert into Branch ( branchNo, branchName ,branchAddress ,cityID , postcode ,telNo) 
	values(2, 'To-Rent Glasgow', '39 Nith Street', 2, 'G122AAS', 07836438946);
	insert into Branch ( branchNo, branchName ,branchAddress ,cityID , postcode ,telNo) 
	values(3, 'To-Rent Cardif', 'Forgeside House, Forgeside', 3, 'CF245FA', 08450805104);
	insert into Branch ( branchNo, branchName ,branchAddress ,cityID , postcode ,telNo) 
	values(4, 'To-Rent Bristol', '9 Dunstan Road', 4, 'TA81ER', 01454414809);

	insert into Staff ( staffID, staffName, staffSalary , staffPosition, branchID)
	values(1, 'Jamie Larson',2500,'Branch Manager',1);
	insert into Staff ( staffID, staffName, staffSalary , staffPosition, branchID)
	values(2, 'John Smith',1500,'Estate Agent ',2);
	insert into Staff ( staffID, staffName, staffSalary , staffPosition, branchID)
	values(3, 'Milli Dickerson',1000,'Cleaner',3);
	insert into Staff ( staffID, staffName, staffSalary , staffPosition, branchID)
	values(4, 'Jack Harson',2000,'Asistent Manager',4);
	insert into Staff ( staffID, staffName, staffSalary , staffPosition, branchID)
	values(5, 'Jacke Larson',1500,'Estate Agent',1);
	insert into Staff ( staffID, staffName, staffSalary , staffPosition, branchID)
	values(6, 'Steeve Wander',1500,'Estate Agent',3);
	insert into Staff ( staffID, staffName, staffSalary , staffPosition, branchID)
	values(7, 'Mara Fasuo',1500,'Estate Agent',4);

	insert into Client ( clientId, clientName, telephoneNumber, email, preAccType, maxRent, staffID, branchID)
	values(1, 'Will Smith', 0772334256, 'WillSmith@gmail.com','2 rooms flat', 3250, 2, 2);
	insert into Client ( clientId, clientName, telephoneNumber, email, preAccType, maxRent, staffID, branchID)
	values(2, 'Florin Salam', 0774338756, 'FlorinSalam@gmail.com','1 bedrooms flat', 3500, 5, 1);
	insert into Client ( clientId, clientName, telephoneNumber, email, preAccType, maxRent, staffID, branchID)
	values(3, 'Bulgur Gregos', 07756609238, 'sajime92@gmail.com','2 bedroom house', 3500, 6, 3);
	insert into Client ( clientId, clientName, telephoneNumber, email, preAccType, maxRent, staffID, branchID)
	values(4, 'Gutu Theodosie', 07787752309, 'GutuTH98@gmail.com','1 ', 3501, 7, 4);
	
	insert into Owner ( ownerID , ownerName , ownerAddress ,ownerPostcode  ,telephoneNumber ,owneremail )
	values(1, 'Mirculescu Ion', 'Flat 7 Reesha Apartments','E141AU', 07764492384,'Ionmiculescu@yahoo.ro');
	insert into Owner ( ownerID , ownerName , ownerAddress ,ownerPostcode  ,telephoneNumber ,owneremail )
	values(2, 'Madison Lee', ' 4 Nickelby Apartments','E121AU', 07753463424,'LeeMadison@yahoo.ro');
	insert into Owner ( ownerID , ownerName , ownerAddress ,ownerPostcode  ,telephoneNumber ,owneremail )
	values(3, 'Heik Midget', '54 Barnard Street','SM67BT', 07768867340,'MidgetMe@yahoo.ro');
	insert into Owner ( ownerID , ownerName , ownerAddress ,ownerPostcode  ,telephoneNumber ,owneremail )
	values(4, 'Heinz Holf', '3 Kings Road','ER45MN', 07723457563,'Hof1992@yahoo.com');

	insert into Property ( propertyID, propertyAddress ,propertyCityID ,propertyPostcode , propertyType , noofrooms, monthlyRent ,ownerID )
	values(1, '34 Barger Street', 1,'cr03pn', 'House',2, 1000, 1);
	insert into Property ( propertyID, propertyAddress ,propertyCityID ,propertyPostcode , propertyType , noofrooms, monthlyRent ,ownerID )
	values(2, '4 Summer', 2,'CF435DS', 'House',4, 2000, 2);
	insert into Property ( propertyID, propertyAddress ,propertyCityID ,propertyPostcode , propertyType , noofrooms, monthlyRent ,ownerID )
	values(3, '33 Micham Road', 3,'PN34DC', 'Flat',2, 1500, 4);
	insert into Property ( propertyID, propertyAddress ,propertyCityID ,propertyPostcode , propertyType , noofrooms, monthlyRent ,ownerID )
	values(4, '43 Spring Road', 4,'RDC23DS', 'Flat',1, 1250, 3);

	insert into Lease ( leaseNumber, clientID, ownerID, propertyID, methodofpay, depositPaidStatus, startDate , endDate  )
	values(1, 4, 2, 2, 'Direct debit','P' , '11-01-2020', '02-04-2021');
	insert into Lease ( leaseNumber, clientID, ownerID, propertyID, methodofpay, depositPaidStatus, startDate , endDate  )
	values(2, 3, 1, 1, 'Direct debit','P' , '11-01-2020', '02-04-2021');
	insert into Lease ( leaseNumber, clientID, ownerID, propertyID, methodofpay, depositPaidStatus, startDate , endDate  )
	values(3, 2, 4, 3, 'Direct debit','U' , '09-02-2020', '09-01-2021');
	insert into Lease ( leaseNumber, clientID, ownerID, propertyID, methodofpay, depositPaidStatus, startDate , endDate  )
	values(4, 1, 3, 4, 'Direct debit','P' , '03-04-2020', '09-23-2021');



	/* We want to find out the best payed region and the best salary for the staff members*/
	Select RegionName, d.staffsalary from region a inner join city b  on a.regionid=b.regionid 
									inner join branch c on b.cityid=c.cityid
									inner join staff d on c.branchno=d.branchid
								    group by  regionname, d.staffsalary having d.staffsalary=(select max(staffsalary)from staff)


	/*We want to find out the owner's name, staff name (branch, city, region), property name, client name and email address where the deposit satus is unpaid*/
	select c.ownerName, e.staffname, f.branchname, g.cityname, h.regionname, b.propertyaddress, d.clientname, d.email 
	from lease a inner join property b on  a.propertyid= b.propertyid and a.ownerID=b.ownerID 
	inner join Owner c on c.ownerID = b.ownerID and a.ownerID=c.ownerID
	inner join client d on a.clientID = d.clientId
	inner join Staff e on d.staffID = e.staffID
	inner join Branch f on f.branchno= e.branchID
	inner join City g on g.cityId= f.cityID 
	inner join Region h on h.regionId = g.regionID 
	where DepositPaidStatus= 'U';





	-- Monthly rent should be greater or equal than 200
	declare @v_monthlyrent int ; 
	begin transaction;
	update Property set  monthlyRent= 50 where propertyID=1
	select @v_monthlyrent= count (*) from Property where monthlyRent<=200 
	if @v_monthlyrent >0
	begin 
	rollback transaction
	print 'Monthly rent should be greater or equal than 200' end 
	else 
	begin 
	commit transaction
	print 'monthly rent has been altered ' 
	end 





	--Updating the maxrent with 4000 but rollingback when updating maxrent to 2000
	begin transaction
	update Client set maxRent= 4000 where clientId=1 
	commit transaction;
	update Client set maxRent= 2000 where clientId=1
	commit transaction; 
	rollback;
